﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Configuration;
using PIM.Utilities.DataAccess;
using PIM.API;
using System.Collections;
using System.Data;
using System.Net.Mail;
using System.Net;

/// <summary>
/// Summary description for PIMService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class PIMService : System.Web.Services.WebService {
    public string ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"].ToString();
    private PIMManager dbmanager = null;
    public PIMService () {

        dbmanager = new PIMManager(ConnectionString);
        dbmanager.Open();
    }


    #region RecipeUser
    /// <summary>
    /// Get RecipeUser
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<RecipeUser> GetRecipeUser(string UserId)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", 0);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUser", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                recipe.UserID = Convert.ToInt32(dr["Userid"].ToString());
                lstRecipt.Add(recipe);
            }
        }

        return lstRecipt;
    }

    [WebMethod]
    public DataTable getIngredientNameByRecipeIdList(string RecipeId)
    {
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", RecipeId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeIngredientUserByRecipeId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
            return _dt;
        else
            return null;
    }

    /// <summary>
    /// Get RecipeUser with paging
    /// </summary>
    /// <param name="UserId"></param>
    /// <param name="page_no"></param>
    /// <param name="page_lenght"></param>
    /// <returns></returns>
    [WebMethod]
    public List<RecipeUser> GetRecipeUserPaging(string UserId, string page_no, string page_lenght)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", 0);
        inputHtable.Add("UserId", UserId);
        inputHtable.Add("page_no", page_no);
        inputHtable.Add("page_lenght", page_lenght);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetAllRecipeUser_Paging", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                recipe.UserID = Convert.ToInt32(dr["Userid"].ToString());
                lstRecipt.Add(recipe);
            }
        }

        return lstRecipt;
    }

    /// <summary>
    /// Get GetRecipeUserByRecipeId
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<RecipeUser> GetRecipeUserByRecipeId(string UserId, string RecipeId)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", RecipeId);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUser", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                recipe.UserID = Convert.ToInt32(dr["Userid"].ToString());
                recipe.IngredientNameString = dr["IngredientName"].ToString();

                lstRecipt.Add(recipe);
            }
        }

        return lstRecipt;
    }

    /// <summary>
    /// Get RecipeUser by RecipeId
    /// </summary>
    /// <param name="RecipeId"></param>
    /// <returns></returns>
    [WebMethod]
    public List<RecipeUser> GetRecipeUserByNamePaging(string RecipeName, string UserId, string page_no, string page_lenght)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeName", RecipeName);
        inputHtable.Add("UserId", UserId);
        inputHtable.Add("page_no", page_no);
        inputHtable.Add("page_lenght", page_lenght);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUserByRecipeNamePaging", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                recipe.IngredientNameString = dr["IngredientName"].ToString();
                recipe.DepartNameString = dr["Depart"].ToString();
                recipe.LinkImage = dr["LinkImage"].ToString();
                lstRecipt.Add(recipe);
            }
        }

        return lstRecipt;
    }

    [WebMethod]
    public RecipeUser GetRecipeUserIngredientDepart(string RecipeId)
    {
        RecipeUser recipe = new RecipeUser();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", RecipeId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUserIngredientDepartByRecipeId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            recipe.CookTime = Convert.ToInt32(_dt.Rows[0]["CookTime"].ToString());
            recipe.Description = _dt.Rows[0]["Description"].ToString();
            recipe.PrepTime = Convert.ToInt32(_dt.Rows[0]["PrepTime"].ToString());
            recipe.RecDirection = _dt.Rows[0]["RecDirection"].ToString();
            recipe.RecipeID = Convert.ToInt32(_dt.Rows[0]["RecipeID"].ToString());
            recipe.RecipeName = _dt.Rows[0]["RecipeName"].ToString();
            recipe.RecipeRating = Convert.ToDouble(_dt.Rows[0]["RecipeRating"].ToString());
            recipe.Serving = Convert.ToInt32(_dt.Rows[0]["Serving"].ToString());
            recipe.RecipeImage = _dt.Rows[0]["Image"].ToString();
            recipe.UserName = _dt.Rows[0]["UserName"].ToString();
            recipe.IngredientNameString = _dt.Rows[0]["IngredientName"].ToString();
            recipe.DepartNameString = _dt.Rows[0]["Depart"].ToString();
            recipe.LinkImage = _dt.Rows[0]["LinkImage"].ToString();
        }

        return recipe;
    }

    [WebMethod]
    public List<RecipeUser> GetRecipeUserByNamePagingWithoutRecipe(string RecipeName, string UserId, string page_no, string page_lenght, string recipeid)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeName", RecipeName);
        inputHtable.Add("UserId", UserId);
        inputHtable.Add("page_no", page_no);
        inputHtable.Add("page_lenght", page_lenght);
        inputHtable.Add("RecipeId", recipeid);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUserByRecipeNamePagingWithoutRecipe", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                recipe.IngredientNameString = dr["IngredientName"].ToString();
                recipe.DepartNameString = dr["Depart"].ToString();
                recipe.LinkImage = dr["LinkImage"].ToString();
                lstRecipt.Add(recipe);
            }
        }

        return lstRecipt;
    }

    /// <summary>
    /// Get RecipeUser by RecipeId
    /// </summary>
    /// <param name="RecipeId"></param>
    /// <returns></returns>
    [WebMethod]
    public List<RecipeUser> GetRecipeUserByName(string RecipeName, string UserId)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeName", RecipeName);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUserByRecipeName", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                lstRecipt.Add(recipe);
            }
        }

        return lstRecipt;
    }

    [WebMethod]
    public RecipeUser getRecipeUserDetail(int recipeID)
    {
        RecipeUser recipeUser = new RecipeUser();
        string sql = string.Format("SELECT * FROM v_RecipeUserIngredient WHERE RECIPEID={0}", recipeID);
        try
        {
            DataRow dr = dbmanager.GetDataRow(sql);
            if (dr != null)
            {
                recipeUser.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipeUser.RecipeName = dr["RecipeName"].ToString();
                recipeUser.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipeUser.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipeUser.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipeUser.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipeUser.RecDirection = dr["RecDirection"].ToString();
                recipeUser.Description = dr["Description"].ToString();
                recipeUser.DepartNameString = dr["Depart"].ToString();
                recipeUser.IngredientNameString = dr["IngredientName"].ToString();
                return recipeUser;
            }

        }
        catch (Exception)
        {

        }
        return null;

    }


    [WebMethod]
    public List<MealPlanRecipeIngredient> GetRecipeIngredientByMealPlanId(string MealPlanId)
    {
        List<MealPlanRecipeIngredient> lstMPRI = new List<MealPlanRecipeIngredient>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanId", MealPlanId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecepeIngredientByMealPlanId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                MealPlanRecipeIngredient recipeByMealPlan = new MealPlanRecipeIngredient();

                recipeByMealPlan.Description = dr["Description"].ToString();
                recipeByMealPlan.MealPlanDetailId = Convert.ToInt32(dr["MealPlanDetailId"].ToString());
                recipeByMealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanId"].ToString());
                recipeByMealPlan.MealPlanName = dr["MealPlanName"].ToString();
                recipeByMealPlan.RecipeId = Convert.ToInt32(dr["RecipeId"].ToString());
                recipeByMealPlan.RecipeIngredientUserId = Convert.ToInt32(dr["UserId"].ToString());
                recipeByMealPlan.RecipeName = dr["RecipeName"].ToString();
                recipeByMealPlan.Serving = Convert.ToInt32(dr["Serving"].ToString());

                lstMPRI.Add(recipeByMealPlan);
            }
        }

        return lstMPRI;
    }

    [WebMethod]
    public RecipeUser GetRecipeUserByNameAndUserId(string RecipeName, int UserId)
    {
        RecipeUser recipe = new RecipeUser();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeName", RecipeName);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUserByRecipeNameAndUserID", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                recipe.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                recipe.Description = dr["Description"].ToString();
                recipe.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                recipe.RecDirection = dr["RecDirection"].ToString();
                recipe.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                recipe.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                recipe.Serving = Convert.ToInt32(dr["Serving"].ToString());
                recipe.UserID = Convert.ToInt32(dr["Userid"].ToString());
                recipe.RecipeImage = dr["Image"].ToString();
                recipe.UserName = dr["UserName"].ToString();
                if (!string.IsNullOrEmpty(dr["isShared"].ToString()))
                    recipe.IsShared = Convert.ToBoolean(dr["isShared"]);
                else
                {
                    recipe.IsShared = false;
                }
            }
            return recipe;
        }

        return null;
    }


    /// <summary>
    /// Get RecipeUser by RecipeId
    /// </summary>
    /// <param name="RecipeId"></param>
    /// <returns></returns>
    [WebMethod]
    public RecipeUser GetRecipeUserByRecipe(int RecipeId)
    {
        RecipeUser _recipeUser = new RecipeUser();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", RecipeId);
        inputHtable.Add("UserId", "0");
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUser", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {

                _recipeUser.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                _recipeUser.Description = dr["Description"].ToString();
                _recipeUser.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                _recipeUser.RecDirection = dr["RecDirection"].ToString();
                _recipeUser.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                _recipeUser.RecipeName = dr["RecipeName"].ToString();
                _recipeUser.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                _recipeUser.Serving = Convert.ToInt32(dr["Serving"].ToString());
                _recipeUser.RecipeIngredientUser = GetRecipeIngredientUser(_recipeUser.RecipeID);
                _recipeUser.RecipeImage = dr["Image"].ToString();
                _recipeUser.IngredientName = dr["IngredientName"].ToString();
                _recipeUser.UserID = Convert.ToInt32(dr["Userid"].ToString());
                _recipeUser.UserName = dr["UserName"].ToString();
                if (!string.IsNullOrEmpty(dr["isShared"].ToString()))
                    _recipeUser.IsShared = Convert.ToBoolean(dr["isShared"]);
                else
                {
                    _recipeUser.IsShared = false;
                }
            }
        }

        return _recipeUser;
    }

    /// <summary>
    /// Get RecipeUser by RecipeId
    /// </summary>
    /// <param name="RecipeId"></param>
    /// <returns></returns>
    [WebMethod]
    public RecipeUser GetRecipeUserByRecipe(int RecipeId, int UserId)
    {
        RecipeUser _recipeUser = new RecipeUser();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeId", RecipeId);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeUser", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {

                _recipeUser.CookTime = Convert.ToInt32(dr["CookTime"].ToString());
                _recipeUser.Description = dr["Description"].ToString();
                _recipeUser.PrepTime = Convert.ToInt32(dr["PrepTime"].ToString());
                _recipeUser.RecDirection = dr["RecDirection"].ToString();
                _recipeUser.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                _recipeUser.RecipeName = dr["RecipeName"].ToString();
                _recipeUser.RecipeRating = Convert.ToDouble(dr["RecipeRating"].ToString());
                _recipeUser.Serving = Convert.ToInt32(dr["Serving"].ToString());
                _recipeUser.RecipeIngredientUser = GetRecipeIngredientUser(_recipeUser.RecipeID);
                _recipeUser.RecipeImage = dr["Image"].ToString();
                _recipeUser.IngredientName = dr["IngredientName"].ToString();
                _recipeUser.UserID = Convert.ToInt32(dr["Userid"].ToString());
                _recipeUser.UserName = dr["UserName"].ToString();
                if (!string.IsNullOrEmpty(dr["isShared"].ToString()))
                    _recipeUser.IsShared = Convert.ToBoolean(dr["isShared"]);
                else
                {
                    _recipeUser.IsShared = false;
                }
            }
        }

        return _recipeUser;
    }

    /// <summary>
    /// Update RecipeUser
    /// </summary>
    /// <param name="recipeId"></param>
    /// <param name="recipeName"></param>
    /// <param name="serving"></param>
    /// <param name="prepTime"></param>
    /// <param name="cookTime"></param>
    /// <param name="photo"></param>
    /// <param name="description"></param>
    /// <param name="recipeRating"></param>
    /// <param name="recDirection"></param>
    /// <returns></returns>
    public string UpdateRecipe(int recipeId, string recipeName, int userId, int serving, int prepTime, int cookTime,
        string photo, string description, double recipeRating, string recDirection, string recipeIngredient, bool isShared)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {

            return (dbAccess.UpdateRecipe(recipeId, recipeName, userId, serving, prepTime, cookTime, photo, description,
                recipeRating, recDirection, recipeIngredient, isShared)).ToString();

        }

    }

    /// <summary>
    /// Delete RecipeUser
    /// </summary>
    /// <param name="recipeID"></param>
    /// <returns></returns>
    public string DeleteRecipe(int recipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteRecipe(recipeID).ToString());
        }

    }

    /// <summary>
    /// Check RecipeUser
    /// </summary>
    /// <param name="recipeID"></param>
    /// <returns></returns>
    public string CheckRecipeOfMealPlan(int recipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.CheckRecipeOfMealPlan(recipeID).ToString());
        }

    }

    /// <summary>
    /// Insert RecipeUser
    /// </summary>
    /// <param name="userId"></param>
    /// <param name="createUserId"></param>
    /// <param name="parentUserId"></param>
    /// <param name="recipeName"></param>
    /// <param name="serving"></param>
    /// <param name="prepTime"></param>
    /// <param name="cookTime"></param>
    /// <param name="photo"></param>
    /// <param name="description"></param>
    /// <param name="recipeRating"></param>
    /// <param name="recDirection"></param>
    /// <returns></returns>
    public int InsertRecipe(int userId, int createUserId, int parentUserId, string recipeName, int serving, int prepTime, int cookTime,
        string photo, string description, double recipeRating, string recDirection, string recipeIngredient, bool isShared)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return dbAccess.InsertRecipe(userId, createUserId, parentUserId, recipeName, serving, prepTime, cookTime
                , photo, description, recipeRating, recDirection, recipeIngredient, isShared);
        }

    }

    [WebMethod]
    public string InsertMyRecipe(string _recipeId, string _userId, string _oldUser)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.InsertMyRecipe(Convert.ToInt32(_recipeId), Convert.ToInt32(_userId), Convert.ToInt32(_oldUser)))
                return "OK";
            else
                return "NotOK";
        }
    }
    #endregion

    #region RecipeIngredientUser
    /// <summary>
    /// Insert RecipeIngredientUser
    /// </summary>
    /// <param name="recipeID"></param>
    /// <param name="ingredient"></param>
    /// <returns></returns>
    public string InsertRecipeIngredientUser(int recipeID, string ingredientIngredientName, int quantity, int units, int direction)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.InsertRecipeIngredientUser(recipeID, ingredientIngredientName, quantity, units, direction).ToString());
        }
    }

    /// <summary>
    /// Delete all recipeIngredientUser
    /// </summary>
    /// <param name="recipeID"></param>
    /// <returns></returns>
    public string DeleteAllRecipeIngredient(int recipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteAllRecipeIngredient(recipeID).ToString());
        }

    }

    [WebMethod]
    private List<RecipeIngredientUser> GetRecipeIngredientUser(int recipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {

            return dbAccess.getRecipeIngredientUser(recipeID);
        }

    }

    [WebMethod]
    public RecipeIngredientUser getRecipeIngredientUserByIngredientId(string IngredientId)
    {
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("IngredientId", IngredientId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_getIngredientUserById", inputHtable, ref outPutHtable, ref obj);
        RecipeIngredientUser RecipeIngredient = new RecipeIngredientUser();
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeIngredient.RecipeIngredientUserID = Convert.ToInt32(dr["RecipeIngredientUserID"].ToString());
                RecipeIngredient.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                RecipeIngredient.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
                RecipeIngredient.Units = Convert.ToInt32(dr["Units"].ToString());
                RecipeIngredient.IngredientName = dr["IngredientName"].ToString();
                RecipeIngredient.Directions = dr["Directions"].ToString();
            }

        }
        return RecipeIngredient;

    }

    [WebMethod]
    public List<RecipeIngredientUser> getRecipeIngredientUserByRecipeID(string RecipeID)
    {
        List<RecipeIngredientUser> list = new List<RecipeIngredientUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeID", RecipeID);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeIngredientUser", inputHtable, ref outPutHtable, ref obj);

        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeIngredientUser RecipeIngredient = new RecipeIngredientUser();
                RecipeIngredient.RecipeIngredientUserID = Convert.ToInt32(dr["RecipeIngredientUserID"].ToString());
                RecipeIngredient.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                RecipeIngredient.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
                RecipeIngredient.Units = Convert.ToInt32(dr["Units"].ToString());
                RecipeIngredient.IngredientName = dr["IngredientName"].ToString();
                RecipeIngredient.Directions = dr["Directions"].ToString();
                RecipeIngredient.RecipeImage = dr["Image"].ToString();
                RecipeIngredient.RecipeName = dr["RecipeName"].ToString();
                RecipeIngredient.Serving = dr["Serving"].ToString();
                list.Add(RecipeIngredient);
            }

        }
        return list;
    }

    [WebMethod]
    public List<RecipeIngredientUser> getRecipeIngredientUserByRecipeIDServing(string RecipeID, string Serving)
    {
        List<RecipeIngredientUser> list = new List<RecipeIngredientUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("RecipeID", RecipeID);
        inputHtable.Add("NewServing", Serving);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetRecipeIngredientUserServing", inputHtable, ref outPutHtable, ref obj);

        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeIngredientUser RecipeIngredient = new RecipeIngredientUser();
                RecipeIngredient.RecipeIngredientUserID = Convert.ToInt32(dr["RecipeIngredientUserID"].ToString());
                RecipeIngredient.RecipeID = Convert.ToInt32(dr["RecipeID"].ToString());
                RecipeIngredient.Quantity = Convert.ToInt32(dr["Quantity"].ToString());
                RecipeIngredient.Units = Convert.ToInt32(dr["Units"].ToString());
                RecipeIngredient.IngredientName = dr["IngredientName"].ToString();
                RecipeIngredient.Directions = dr["Directions"].ToString();
                RecipeIngredient.RecipeImage = dr["Image"].ToString();
                RecipeIngredient.RecipeName = dr["RecipeName"].ToString();
                RecipeIngredient.Serving = dr["Serving"].ToString();
                list.Add(RecipeIngredient);
            }

        }
        return list;
    }

    [WebMethod]
    public List<IngredientDept> getIngredientDept(string ingredientName)
    {
        List<IngredientDept> list = new List<IngredientDept>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("str", ingredientName);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetGloceryListWithDept", inputHtable, ref outPutHtable, ref obj);

        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                IngredientDept ingredient = new IngredientDept();
                ingredient.GroDept = dr["GroDept"].ToString();
                list.Add(ingredient);
            }

        }
        return list;
    }

    [WebMethod]
    public List<IngredientDeptDetail> getIngredientDeptDetail(string ingredientName, string GroDept)
    {
        List<IngredientDeptDetail> list = new List<IngredientDeptDetail>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("str", ingredientName);
        inputHtable.Add("GroDept", GroDept);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetGloceryListWithDeptDetail", inputHtable, ref outPutHtable, ref obj);

        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                IngredientDeptDetail ingredientDetail = new IngredientDeptDetail();
                ingredientDetail.GroDept = dr["GroDept"].ToString();
                ingredientDetail.ingre = dr["ingre"].ToString();
                list.Add(ingredientDetail);
            }

        }
        return list;
    }

    [WebMethod]
    public string DeleteIngredientUser(string _IngredientID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteIngredientUserByIngredientID(_IngredientID).ToString());
        }
    }


    #endregion


    #region PlanItMeal

    [WebMethod]
    public string InsertMyMealPlan(string _mealPlanId, string _userId, string _oldUser)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.InsertMyMealPlan(Convert.ToInt32(_mealPlanId), Convert.ToInt32(_userId), Convert.ToInt32(_oldUser)))
                return "OK";
            else
                return "NotOK";
        }
    }

    [WebMethod]
    public string CreateMealPlan(string UserID, string MealPlanName, string recipeidlist)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.CheckMealPlanName(MealPlanName) == "1")
            {
                return "Meal plan name is avalable in system!";
            }
            else
            {
                if (dbAccess.CreateMealPlan(UserID, MealPlanName, recipeidlist))
                    return "Create meal plan is successful";
                else
                    return "Create meal plan is successful";
            }
        }
    }

    [WebMethod]
    public string CreateNutrition(string UserID, string recipeidlist)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return dbAccess.CreateNutrition(UserID, recipeidlist).ToString();
        }
    }

    public int InsertPlanItMeal(int _userId, int _createdUserId, int _parentUserId, string _mealPlanName, bool _isPublished, bool _canCopy, double _mealPlanRating,
            int _avgCalPerDay, int _estCostPerDay, string _mealPlanPhoto, int _noOfReview, string _description, string _basicInfo)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.InsertMealPlan(_userId, _createdUserId, _parentUserId, _mealPlanName, _isPublished, _canCopy, _mealPlanRating, _avgCalPerDay, _estCostPerDay, _mealPlanPhoto, _noOfReview, _description, _basicInfo));
        }
    }

    public bool InsertPlanItMealDetail(int _mealPlanID, int _mealTypeID, int _recipeID, int _groceryItemID, int _planTypeID, int _noOfServings,
            DateTime _dateCreated, string _dayOfWeek, string _userId)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.InsertMealPlanDetail(_mealPlanID, _mealTypeID, _recipeID, _groceryItemID, _planTypeID, _noOfServings, _dateCreated, _dayOfWeek, _userId));
        }
    }
    #endregion

    #region MealPlan

    [WebMethod]
    public MealPlan GetMealPlanByMealPlanId(string MealPlanId)
    {
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanId", MealPlanId);
        MealPlan _MealPlan = new MealPlan();

        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlanByMealPlanId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {

                _MealPlan.AvgCalPerDay = Convert.ToInt32(dr["AvgCalPerDay"]);
                _MealPlan.canCopy = Convert.ToBoolean(dr["canCopy"]);
                _MealPlan.CreatedUserID = Convert.ToInt32(dr["CreatedUserID"]);
                _MealPlan.EstCostPerDay = Convert.ToInt32(dr["EstCostPerDay"]);
                _MealPlan.isShared = Convert.ToBoolean(dr["isShared"]);
                _MealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanID"]);
                _MealPlan.MealPlanName = dr["MealPlanName"].ToString();
                _MealPlan.MealPlanPhoto = dr["MealPlanPhoto"].ToString();
                _MealPlan.MealPlanRating = Convert.ToDouble(dr["MealPlanRating"]);
                _MealPlan.NoOfReview = Convert.ToInt32(dr["NoOfReview"]);
                _MealPlan.ParentUserId = Convert.ToInt32(dr["ParentUserID"]);
                _MealPlan.UserID = Convert.ToInt32(dr["UserID"]);
                _MealPlan.Description = dr["Description"].ToString();
                _MealPlan.BasicInfo = dr["BasicInfo"].ToString();
            }
        }
        return _MealPlan;
    }

    [WebMethod]
    public MealPlan GetMealPlanByMealPlanId(string MealPlanId,string UserId)
    {
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanId", MealPlanId);
        inputHtable.Add("UserId", UserId);
        MealPlan _MealPlan = new MealPlan();

        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlanByMealPlanIdUserId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {

                _MealPlan.AvgCalPerDay = Convert.ToInt32(dr["AvgCalPerDay"]);
                _MealPlan.canCopy = Convert.ToBoolean(dr["canCopy"]);
                _MealPlan.CreatedUserID = Convert.ToInt32(dr["CreatedUserID"]);
                _MealPlan.EstCostPerDay = Convert.ToInt32(dr["EstCostPerDay"]);
                _MealPlan.isShared = Convert.ToBoolean(dr["isShared"]);
                _MealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanID"]);
                _MealPlan.MealPlanName = dr["MealPlanName"].ToString();
                _MealPlan.MealPlanPhoto = dr["MealPlanPhoto"].ToString();
                _MealPlan.MealPlanRating = Convert.ToDouble(dr["MealPlanRating"]);
                _MealPlan.NoOfReview = Convert.ToInt32(dr["NoOfReview"]);
                _MealPlan.ParentUserId = Convert.ToInt32(dr["ParentUserID"]);
                _MealPlan.UserID = Convert.ToInt32(dr["UserID"]);
                _MealPlan.Description = dr["Description"].ToString();
                _MealPlan.BasicInfo = dr["BasicInfo"].ToString();
            }
        }
        return _MealPlan;
    }

    [WebMethod]
    public List<MealPlan> GetMealPlan(string MealPlanId, string UserId)
    {
        List<MealPlan> lstMealPlan = new List<MealPlan>();

        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanId", MealPlanId);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlan", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                MealPlan _MealPlan = new MealPlan();
                _MealPlan.AvgCalPerDay = Convert.ToInt32(dr["AvgCalPerDay"]);
                _MealPlan.canCopy = Convert.ToBoolean(dr["canCopy"]);
                _MealPlan.CreatedUserID = Convert.ToInt32(dr["CreatedUserID"]);
                _MealPlan.EstCostPerDay = Convert.ToInt32(dr["EstCostPerDay"]);
                _MealPlan.isShared = Convert.ToBoolean(dr["isShared"]);
                _MealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanID"]);
                _MealPlan.MealPlanName = dr["MealPlanName"].ToString();
                _MealPlan.MealPlanPhoto = dr["MealPlanPhoto"].ToString();
                _MealPlan.MealPlanRating = Convert.ToDouble(dr["MealPlanRating"]);
                _MealPlan.NoOfReview = Convert.ToInt32(dr["NoOfReview"]);
                _MealPlan.ParentUserId = Convert.ToInt32(dr["ParentUserID"]);
                _MealPlan.UserID = Convert.ToInt32(dr["UserID"]);
                _MealPlan.Description = dr["Description"].ToString();
                _MealPlan.BasicInfo = dr["BasicInfo"].ToString();
                lstMealPlan.Add(_MealPlan);
            }
        }

        return lstMealPlan;
    }

    [WebMethod]
    public MealPlanIngredientDepart GetIngredientDepartByMealPlanId(string MealPlanId)
    {
        MealPlanIngredientDepart MealPlanIngredient = new MealPlanIngredientDepart();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanId", MealPlanId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetIngredientDepartmentListByMealPlanId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            MealPlanIngredient.MealPlanID = Convert.ToInt32(_dt.Rows[0]["MealPlanID"].ToString());
            MealPlanIngredient.ListDepartment = _dt.Rows[0]["ListDepartment"].ToString();
            MealPlanIngredient.ListIngredient = _dt.Rows[0]["ListIngredient"].ToString();
        }

        return MealPlanIngredient;
    }

    [WebMethod]
    public List<MealPlan> GetMealPlanPaging(string MealPlanId, string UserId, string page_no, string page_lenght)
    {
        List<MealPlan> lstMealPlan = new List<MealPlan>();

        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanId", MealPlanId);
        inputHtable.Add("UserId", UserId);
        inputHtable.Add("page_no", page_no);
        inputHtable.Add("page_lenght", page_lenght);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlan_Paging", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                MealPlan _MealPlan = new MealPlan();
                _MealPlan.AvgCalPerDay = Convert.ToInt32(dr["AvgCalPerDay"]);
                _MealPlan.canCopy = Convert.ToBoolean(dr["canCopy"]);
                _MealPlan.CreatedUserID = Convert.ToInt32(dr["CreatedUserID"]);
                _MealPlan.EstCostPerDay = Convert.ToInt32(dr["EstCostPerDay"]);
                _MealPlan.isShared = Convert.ToBoolean(dr["isShared"]);
                _MealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanID"]);
                _MealPlan.MealPlanName = dr["MealPlanName"].ToString();
                _MealPlan.MealPlanPhoto = dr["MealPlanPhoto"].ToString();
                _MealPlan.MealPlanRating = Convert.ToDouble(dr["MealPlanRating"]);
                _MealPlan.NoOfReview = Convert.ToInt32(dr["NoOfReview"]);
                _MealPlan.ParentUserId = Convert.ToInt32(dr["ParentUserID"]);
                _MealPlan.UserID = Convert.ToInt32(dr["UserID"]);
                _MealPlan.Description = dr["Description"].ToString();
                lstMealPlan.Add(_MealPlan);
            }
        }

        return lstMealPlan;
    }

    [WebMethod]
    public List<RecipeUser> GetRecipeUserByMealPlan(int MealPlanId)
    {
        List<RecipeUser> lRecipe = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanID", MealPlanId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlanDetailInfoFromMealPlanID", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe = GetRecipeUserByRecipe(Convert.ToInt32(dr["RecipeID"]));

                lRecipe.Add(recipe);
            }
        }
        return lRecipe;
    }

    [WebMethod]
    public List<MealPlan> GetMealPlanByName(string MealPlanName, string UserId)
    {
        List<MealPlan> lstMealPlan = new List<MealPlan>();

        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanName", MealPlanName);
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlanByName", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                MealPlan _MealPlan = new MealPlan();
                _MealPlan.AvgCalPerDay = Convert.ToInt32(dr["AvgCalPerDay"]);
                _MealPlan.canCopy = Convert.ToBoolean(dr["canCopy"]);
                _MealPlan.CreatedUserID = Convert.ToInt32(dr["CreatedUserID"]);
                _MealPlan.EstCostPerDay = Convert.ToInt32(dr["EstCostPerDay"]);
                _MealPlan.isShared = Convert.ToBoolean(dr["isShared"]);
                _MealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanID"]);
                _MealPlan.MealPlanName = dr["MealPlanName"].ToString();
                _MealPlan.MealPlanPhoto = dr["MealPlanPhoto"].ToString();
                _MealPlan.MealPlanRating = Convert.ToDouble(dr["MealPlanRating"]);
                _MealPlan.NoOfReview = Convert.ToInt32(dr["NoOfReview"]);
                _MealPlan.ParentUserId = Convert.ToInt32(dr["ParentUserID"]);
                _MealPlan.UserID = Convert.ToInt32(dr["UserID"]);
                _MealPlan.Description = dr["Description"].ToString();
                _MealPlan.UserName = dr["UserName"].ToString();
                lstMealPlan.Add(_MealPlan);
            }
        }

        return lstMealPlan;
    }



    [WebMethod]
    public List<MealPlan> GetMealPlanByNamePagin(string MealPlanName, string UserId, string page_no, string page_lenght)
    {
        List<MealPlan> lstMealPlan = new List<MealPlan>();

        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("MealPlanName", MealPlanName);
        inputHtable.Add("UserId", UserId);
        inputHtable.Add("page_no", page_no);
        inputHtable.Add("page_lenght", page_lenght);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetMealPlanByName_paging", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                MealPlan _MealPlan = new MealPlan();
                _MealPlan.AvgCalPerDay = Convert.ToInt32(dr["AvgCalPerDay"]);
                _MealPlan.canCopy = Convert.ToBoolean(dr["canCopy"]);
                _MealPlan.CreatedUserID = Convert.ToInt32(dr["CreatedUserID"]);
                _MealPlan.EstCostPerDay = Convert.ToInt32(dr["EstCostPerDay"]);
                _MealPlan.isShared = Convert.ToBoolean(dr["isShared"]);
                _MealPlan.MealPlanID = Convert.ToInt32(dr["MealPlanID"]);
                _MealPlan.MealPlanName = dr["MealPlanName"].ToString();
                _MealPlan.MealPlanPhoto = dr["MealPlanPhoto"].ToString();
                _MealPlan.MealPlanRating = Convert.ToDouble(dr["MealPlanRating"]);
                _MealPlan.NoOfReview = Convert.ToInt32(dr["NoOfReview"]);
                _MealPlan.ParentUserId = Convert.ToInt32(dr["ParentUserID"]);
                _MealPlan.UserID = Convert.ToInt32(dr["UserID"]);
                _MealPlan.Description = dr["Description"].ToString();
                _MealPlan.UserName = dr["UserName"].ToString();
                _MealPlan.ListRecipeId = dr["ListRecipeId"].ToString();
                _MealPlan.ImageLink = dr["LinkImage"].ToString();
                lstMealPlan.Add(_MealPlan);
            }
        }

        return lstMealPlan;
    }

    #region GetSearchRecipeName
    /// <summary>
    /// Get GetSearchRecipeName
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<RecipeUser> GetSearchRecipeName(string UserId)
    {
        List<RecipeUser> lstRecipt = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("UserId", UserId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetSearchRecipeName", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow dr in _dt.Rows)
            {
                RecipeUser recipe = new RecipeUser();
                recipe.RecipeID = Convert.ToInt32(dr["NDB_No"].ToString());
                recipe.RecipeName = dr["RecipeName"].ToString();
                lstRecipt.Add(recipe);
            }
        }
        return lstRecipt;
    }
    #endregion
    #region UnitReport
    /// <summary>
    /// Get UnitReport
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<Unit> GetUnitReport(string UserId, string RecipeName)
    {
        if (UserId != "")
        {
            List<Unit> lstUnitReport = new List<Unit>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("RecipeName", RecipeName.Replace(",,", "'"));
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_PIM_GetUnit", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    Unit UnitReport = new Unit();
                    UnitReport.NDB_No = dr["NDB_No"].ToString();
                    UnitReport.Msre_Desc = dr["Msre_Desc"].ToString();
                    UnitReport.Gm_Wgt = float.Parse(dr["Gm_Wgt"].ToString());

                    lstUnitReport.Add(UnitReport);
                }
            }

            return lstUnitReport;
        }
        else
            return null;
    }
    #endregion
    #region CurrentNutrient
    /// <summary>
    /// Get CurrentNutrient
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<CurrentNutrient> GetCurrentNutrient(string UserId, string RecipeName, string UnitName)
    {
        if (UserId != "")
        {
            List<CurrentNutrient> lstCurrentNutrientReport = new List<CurrentNutrient>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("RecipeName", RecipeName.Replace(",,", "'"));
            inputHtable.Add("UnitName", UnitName);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_PIM_GetAllValueReports", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    CurrentNutrient CurrentNutrientReport = new CurrentNutrient();
                    CurrentNutrientReport.Cal = dr["Cal"].ToString() == null ? 0 : float.Parse(dr["Cal"].ToString());
                    CurrentNutrientReport.Protein = dr["Protein"].ToString() == null ? 0 : float.Parse(dr["Protein"].ToString());
                    CurrentNutrientReport.Fat = dr["Fat"].ToString() == null ? 0 : float.Parse(dr["Fat"].ToString());
                    CurrentNutrientReport.Carb = dr["Carb"].ToString() == null ? 0 : float.Parse(dr["Carb"].ToString());
                    CurrentNutrientReport.Weight = dr["Weight"].ToString() == null ? 0 : float.Parse(dr["Weight"].ToString());
                    CurrentNutrientReport.RecipeID = dr["RecipeID"].ToString() == null ? 0 : Convert.ToInt32(dr["RecipeID"].ToString());
                    lstCurrentNutrientReport.Add(CurrentNutrientReport);
                }
            }

            return lstCurrentNutrientReport;
        }
        else
            return null;
    }
    #endregion
    #region Report1
    /// <summary>
    /// Get Report1
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<Report1> GetReport1(string UserId)
    {
        if (UserId != "")
        {
            List<Report1> lstReport1 = new List<Report1>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("UserId", UserId);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_Rpt_Today_Calorie1", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    Report1 report1 = new Report1();
                    report1.Name = dr["Name"].ToString();
                    report1.Today = float.Parse(dr["Today"].ToString());
                    report1.Target = float.Parse(dr["Target"].ToString());
                    lstReport1.Add(report1);
                }
            }

            return lstReport1;
        }
        else
            return null;
    }
    #endregion
    #region Report2
    /// <summary>
    /// Get Report2
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<Report2> GetReport2(string UserId, string Date)
    {
        if (UserId != "")
        {
            List<Report2> lstReport2 = new List<Report2>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("UserId", UserId);
            inputHtable.Add("Date", Date);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_Rpt_Today_Calorie2", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    Report2 report2 = new Report2();
                    report2.Name = dr["Name"].ToString();
                    report2.Calorie = dr["Calorie"].ToString() == null || dr["Calorie"].ToString() == "" ? 0 : float.Parse(dr["Calorie"].ToString());
                    //report2.CalorieTarget = dr["CalorieTarget"].ToString() == null || dr["CalorieTarget"].ToString() == "" ? 0 : float.Parse(dr["CalorieTarget"].ToString());
                    report2.Carb = dr["Carb"].ToString() == null || dr["Carb"].ToString() == "" ? 0 : float.Parse(dr["Carb"].ToString());
                    report2.Fat = dr["Fat"].ToString() == null || dr["Fat"].ToString() == "" ? 0 : float.Parse(dr["Fat"].ToString());
                    report2.Protein = dr["Protein"].ToString() == null || dr["Protein"].ToString() == "" ? 0 : float.Parse(dr["Protein"].ToString());
                    lstReport2.Add(report2);
                }
            }

            return lstReport2;
        }
        else
            return null;
    }
    #endregion
    #region ReportExercise
    /// <summary>
    /// Get ReportExercise
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<ReportExercise> GetReportExercise(string UserId, string Date)
    {
        if (UserId != "")
        {
            List<ReportExercise> lstReport5 = new List<ReportExercise>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("UserId", UserId);
            inputHtable.Add("Date", Date);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_Rpt_MyExerciseGoal5", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    ReportExercise report5 = new ReportExercise();
                    report5.Name = dr["Name"].ToString();
                    report5.YourExercise = dr["Cal"].ToString() == null || dr["Cal"].ToString() == "" ? 0 : float.Parse(dr["Cal"].ToString());
                    report5.Target = dr["Target"].ToString() == null || dr["Target"].ToString() == "" ? 0 : float.Parse(dr["Target"].ToString());
                    lstReport5.Add(report5);
                }
            }

            return lstReport5;
        }
        else
            return null;
    }
    #endregion
    #region ReportWeight
    /// <summary>
    /// Get Report Weight
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<ReportWeight> GetReportWeight(string UserId, string Date)
    {
        if (UserId != "")
        {
            List<ReportWeight> lstReport4 = new List<ReportWeight>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("UserId", UserId);
            inputHtable.Add("Date", Date);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_Rpt_MyWeightGoal4", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    ReportWeight report4 = new ReportWeight();
                    report4.Name = dr["Name"].ToString();
                    report4.YourWeight = dr["YourWeight"].ToString() == null || dr["YourWeight"].ToString() == "" ? 0 : float.Parse(dr["YourWeight"].ToString());
                    report4.Target = dr["Target"].ToString() == null || dr["Target"].ToString() == "" ? 0 : float.Parse(dr["Target"].ToString());
                    lstReport4.Add(report4);
                }
            }

            return lstReport4;
        }
        else
            return null;
    }
    #endregion
    #region ListMyExercises
    /// <summary>
    /// Get ListMyExercises
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<ListMyExercises> GetListMyExercises(string UserId)
    {
        if (UserId != "")
        {
            List<ListMyExercises> lstReportData = new List<ListMyExercises>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("UserId", UserId);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_PIM_ListMyExercises", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    ListMyExercises ReportData = new ListMyExercises();
                    ReportData.ID = Convert.ToInt32(dr["ID"].ToString());
                    ReportData.Header = dr["Header"].ToString();
                    ReportData.Date = dr["Date"].ToString();
                    ReportData.ExerciseName = dr["ExerciseName"].ToString();
                    ReportData.Cal = float.Parse(dr["Cal"].ToString());
                    ReportData.Rank = Convert.ToInt32(dr["Rank"].ToString());
                    lstReportData.Add(ReportData);
                }
            }

            return lstReportData;
        }
        else
            return null;
    }
    #endregion
    #region ListMyWeight
    /// <summary>
    /// Get ListMyWeight
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<ListMyWeight> GetListMyWeight(string UserId)
    {
        if (UserId != "")
        {
            List<ListMyWeight> lstReportData = new List<ListMyWeight>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("UserId", UserId);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_PIM_ListMyWeight", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    ListMyWeight ReportData = new ListMyWeight();
                    ReportData.ID = Convert.ToInt32(dr["ID"].ToString());
                    ReportData.Header = dr["Header"].ToString();
                    ReportData.Date = dr["Date"].ToString();
                    ReportData.Weight = float.Parse(dr["Weight"].ToString());
                    ReportData.Rank = Convert.ToInt32(dr["Rank"].ToString());
                    lstReportData.Add(ReportData);
                }
            }

            return lstReportData;
        }
        else
            return null;
    }
    #endregion
    #region ListMyFoodIntake
    /// <summary>
    /// Get ListMyFoodIntake
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<ListMyFoodIntake> GetListMyFoodIntake(string UserId)
    {
        if (UserId != "")
        {
            List<ListMyFoodIntake> lstReportData = new List<ListMyFoodIntake>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("UserId", UserId);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_PIM_ListMyFoodIntake", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    ListMyFoodIntake ReportData = new ListMyFoodIntake();
                    ReportData.ID = Convert.ToInt32(dr["ID"].ToString());
                    ReportData.Header = dr["Header"].ToString();
                    ReportData.Date = dr["Date"].ToString();
                    ReportData.CalorieName = dr["CalorieName"].ToString();
                    ReportData.Calorie = float.Parse(dr["Calorie"].ToString());
                    ReportData.Fat = float.Parse(dr["Fat"].ToString());
                    ReportData.Carb = float.Parse(dr["Carb"].ToString());
                    ReportData.Protein = float.Parse(dr["Protein"].ToString());
                    ReportData.Rank = Convert.ToInt32(dr["Rank"].ToString());
                    lstReportData.Add(ReportData);
                }
            }

            return lstReportData;
        }
        else
            return null;
    }
    #endregion
    #region ShowMainReport
    /// <summary>
    /// Get ShowMainReport
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public List<ShowMainReport> GetShowMainReport(string UserId, string Date)
    {
        if (UserId != "")
        {
            List<ShowMainReport> lstReportData = new List<ShowMainReport>();
            Hashtable inputHtable = new Hashtable();
            inputHtable.Add("UserId", UserId);
            inputHtable.Add("Date", Date);
            Hashtable outPutHtable = new Hashtable();
            object obj = new object();
            DataTable _dt = dbmanager.GetData("sp_Rpt_ShowMainReport", inputHtable, ref outPutHtable, ref obj);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                foreach (DataRow dr in _dt.Rows)
                {
                    ShowMainReport ReportData = new ShowMainReport();
                    ReportData.Name = dr["Name"].ToString();
                    ReportData.Calorie = dr["Calorie"].ToString() == null || dr["Calorie"].ToString() == "" ? 0 : float.Parse(dr["Calorie"].ToString());
                    ReportData.Fat = dr["Fat"].ToString() == null || dr["Fat"].ToString() == "" ? 0 : float.Parse(dr["Fat"].ToString());
                    ReportData.Carb = dr["Carb"].ToString() == null || dr["Carb"].ToString() == "" ? 0 : float.Parse(dr["Carb"].ToString());
                    ReportData.Protein = dr["Protein"].ToString() == null || dr["Protein"].ToString() == "" ? 0 : float.Parse(dr["Protein"].ToString());
                    ReportData.Weight = dr["Weight"].ToString() == null || dr["Weight"].ToString() == "" ? 0 : float.Parse(dr["Weight"].ToString());
                    ReportData.Exercise = dr["Exercise"].ToString() == null || dr["Exercise"].ToString() == "" ? 0 : float.Parse(dr["Exercise"].ToString());
                    lstReportData.Add(ReportData);
                }
            }

            return lstReportData;
        }
        else
            return null;
    }
    #endregion
    #region InserReportExercises
    /// <summary>
    /// InserReportExercises
    /// </summary>
    /// <returns>List</returns>
    [WebMethod(EnableSession = true)]
    public string InserReportExercises(int _UserID, string _Date, string _ExerciseName, float _Cal)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            int r = dbAccess.InserReportExercises(_UserID, _Date, _ExerciseName, _Cal);
            return r.ToString();
        }
    }
    #endregion
    #region InserReportWeights
    /// <summary>
    /// InserReportWeights
    /// </summary>
    /// <returns>List</returns>
    [WebMethod(EnableSession = true)]
    public string InserReportWeights(int _UserID, string _Date, float _Weight)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            int r = dbAccess.InserReportWeights(_UserID, _Date, _Weight);
            return r.ToString();
        }
    }
    #endregion
    #region DeleteReportExercises
    /// <summary>
    /// DeleteReportExercises
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public string DeleteReportExercises(int UserId, int ID)
    {

        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteReportExercises(UserId, ID).ToString());
        }

    }
    #endregion
    #region DeleteReportWeights
    /// <summary>
    /// DeleteReportWeights
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public string DeleteReportWeights(int UserId, int ID)
    {

        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteReportWeights(UserId, ID).ToString());
        }

    }
    #endregion
    #region DelteReportFoodDrink
    /// <summary>
    /// DelteReportFoodDrink
    /// </summary>
    /// <returns>List</returns>
    [WebMethod]
    public string DelteReportFoodDrink(int UserId, int ID)
    {

        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DelteReportFoodDrink(UserId, ID).ToString());
        }

    }
    #endregion
    #region InsertReportFoodDrink
    /// <summary>
    /// InsertReport Food Drink
    /// </summary>
    /// <returns>List</returns>
    [WebMethod(EnableSession = true)]
    public string InsertReportFoodDrink(int _UserID, string _CalorieName, float _Calorie, float _Fat, float _Carb, float _Protein, int _Quantity, string _Unit, string _Date, int _RecipeID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            int r = dbAccess.InsertReportFoodDrink(_UserID, _CalorieName.Replace(",,", "'"), _Calorie, _Fat, _Carb, _Protein, _Quantity, _Unit.Replace(",,", "'"), _Date, _RecipeID);
            return r.ToString();
        }
    }
    #endregion
    #region InsertReportGoals
    /// <summary>
    /// InsertReportGoals
    /// </summary>
    /// <returns>List</returns>
    [WebMethod(EnableSession = true)]
    public string InsertReportGoals(int _UserID, string _Date, float _Calorie, float _Fat, float _Carb, float _Protein, float _Weight, float _Exercise)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            int r = dbAccess.InsertReportGoals(_UserID, _Date, _Calorie, _Fat, _Carb, _Protein, _Weight, _Exercise);
            return r.ToString();
        }
    }
    #endregion
    #region EditReportGoals
    /// <summary>
    /// EditReportGoals
    /// </summary>
    /// <returns>List</returns>
    [WebMethod(EnableSession = true)]
    public string EditReportGoals(int _UserID, string _Date, float _Calorie, float _Fat, float _Carb, float _Protein, float _Weight, float _Exercise)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            int r = dbAccess.EditReportGoals(_UserID, _Date, _Calorie, _Fat, _Carb, _Protein, _Weight, _Exercise);
            return r.ToString();
        }
    }
    #endregion


    [WebMethod]
    public string DeleteRecipeFromMealPlan(int recipeID)
    {

        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteRecipeFromMealPlan(recipeID).ToString());
        }

    }
    
    [WebMethod]
    public string DeleteMealPlanDetail(int mealPlanID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteMealPlanDetail(mealPlanID).ToString());
        }

    }


    public string UpdateMealPlan(int MealPlanId, int UserID, int CreatedUserID, int ParentUserID, string MealPlanName, bool isPublished, bool canCopy,
        int NoOfReview, double MealPlanRating, int AvgCalPerDay, int EstCostPerDay, string MealPlanPhoto, string Description, string BasicInfo)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {

            return (dbAccess.UpdateMealPlan(MealPlanId, UserID, CreatedUserID, ParentUserID, MealPlanName, isPublished, canCopy,
                     NoOfReview, MealPlanRating, AvgCalPerDay, EstCostPerDay, MealPlanPhoto, Description, BasicInfo)).ToString();

        }

    }
    [WebMethod]
    public string DeletMealPlan(int mealPlanID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {

            return (dbAccess.DeletMealPlan(mealPlanID).ToString());
        }
    }


    #endregion

    #region Glocery

    /// <summary>
    /// 
    /// </summary>
    /// <param name="IngredientName"></param>
    /// <param name="UserID"></param>
    /// <returns></returns>
    [WebMethod]
    public List<IngredientList> addIngredientList(string IngredientName, string UserID)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.InsertIngredient(Convert.ToInt32(UserID), IngredientName))
                return getIngredientList(UserID);
            else
                return null;
        }
    }

    [WebMethod]
    public string DeleteIngredientList(int userid)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.DeleteIngredientListUser(userid).ToString());
        }

    }

    [WebMethod]
    public string getIngredientListByRecipeIdList(string recipeid)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.getIngredientStringByRecipeList(recipeid).ToString());
        }
    }

    public List<IngredientList> getIngredientList(string UserID)
    {
        string sql = string.Format("select * from [IngredientListUser] where CAST(FLOOR(CAST([CreateDate] AS FLOAT )) AS DATETIME)=CAST(FLOOR(CAST(getdate() AS FLOAT )) AS DATETIME) and UserID=" + UserID);
        List<IngredientList> list = new List<IngredientList>();
        try
        {
            DataTable dt = new DataTable();
            dt = dbmanager.GetData(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    IngredientList ingredient = new IngredientList();
                    ingredient.IngredientID = Convert.ToInt32(r["IngredientID"]);
                    ingredient.UserID = Convert.ToInt32(r["UserID"]);
                    ingredient.Long_Desc = r["Long_Desc"].ToString();
                    list.Add(ingredient);
                }
                return list;
            }
        }
        catch (Exception)
        {

        }
        return null;
    }

    #endregion

    #region Authentication

    [WebMethod(EnableSession = true)]
    public string Login(string _userName, string _passWord)
    {
        return signin(_userName, _passWord);
    }

    [WebMethod(EnableSession = true)]
    public string Logout()
    {
        if (Session["UserName"] != null)
            Session["UserName"] = null;
        if (Session["Userid"] != null)
            Session["Userid"] = null;

        return "";
    }

    [WebMethod(EnableSession = true)]
    private string signin(string _userName, string _passWord)
    {
        string sql = "";
        sql = "select Userid, UserName, Password from Users where UserName='" + _userName + "' and Password='" + _passWord + "'";
        try
        {
            DataTable _dt = dbmanager.GetData(sql);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                Session.Add("UserName", _dt.Rows[0]["UserName"]);
                Session.Add("Userid", _dt.Rows[0]["Userid"]);
                return _dt.Rows[0]["UserName"].ToString();
            }
            else
                return "";
        }
        catch
        {
            return "";
        }
    }

    [WebMethod(EnableSession = true)]
    private string CheckUser(string _userName)
    {
        string sql = "";
        sql = "select Userid, UserName, Password from Users where upper(UserName)=upper('" + _userName + "')";
        try
        {
            DataTable _dt = dbmanager.GetData(sql);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                return "true";
            }
            else
                return "false";
        }
        catch
        {
            return "false";
        }
    }

    [WebMethod(EnableSession = true)]
    public string CreateNewMember(string _userName, string _password)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            int r = dbAccess.CreateNewUser(_userName, _password);
            if (r == 1)
                return signin(_userName, _password);
            else
                return r.ToString();
        }
    }
   

    [WebMethod]
    public Users GetUserById(string _userId)
    {
        Users us = new Users();


        //List<RecipeUser> lRecipe = new List<RecipeUser>();
        Hashtable inputHtable = new Hashtable();
        inputHtable.Add("UserId", _userId);
        Hashtable outPutHtable = new Hashtable();
        object obj = new object();
        DataTable _dt = dbmanager.GetData("sp_PIM_getUserByUserId", inputHtable, ref outPutHtable, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            us.Password = _dt.Rows[0]["Password"].ToString();
            us.UserName = _dt.Rows[0]["UserName"].ToString();
            us.UserID = _dt.Rows[0]["Userid"].ToString();
        }
        return us;
    }

    [WebMethod]
    public string ChangePassword(string _userId, string _password)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            if (dbAccess.ChangPassword(_userId, _password))
                return "OK";
            else
                return "NotOK";
        }
    }

    [WebMethod]
    public Users CheckEmail(string email)
    {
        Users us = new Users();
        try
        {
            string sql = string.Format("Select * from Users where UserName='{0}'", email);
            DataTable _dt = new DataTable();
            _dt = dbmanager.GetData(sql);
            if (_dt != null && _dt.Rows.Count > 0)
            {
                us.Password = _dt.Rows[0]["Password"].ToString();
                us.UserName = _dt.Rows[0]["UserName"].ToString();
                us.UserID = _dt.Rows[0]["Userid"].ToString();
                return us;
            }



        }
        catch (Exception)
        {
        }
        return null;
    }

    #endregion


    #region rating
    [WebMethod]
    public string Rating(int objectTypeID, int objectID, int rating)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            return (dbAccess.Rating(objectTypeID, objectID, rating).ToString());
        }
    }

    #endregion

    #region Session
    [WebMethod(EnableSession = true)]
    public string SaveSession(string key, string value)
    {
        try
        {
            Session.Add(key, value);
            return "OK";
        }
        catch (Exception ex)
        {
            return ex.ToString();
        }

    }

    [WebMethod(EnableSession = true)]
    public void RemoveSession(string key)
    {
        //string _userId = Session["Userid"].ToString();

        //using (DataAccess dbAccess = new DataAccess(ConnectionString))
        //{
        //    dbAccess.DeleteIngredientListUser(Convert.ToInt32(_userId));
        //}

        string[] keyname = key.Split('|');
        if (keyname.Count() > 0)
        {
            foreach (string kn in keyname)
            {
                Session.Remove(kn);
            }
        }

    }
    #endregion

    [WebMethod]
    public string SendEmailFeedback(string title, string comment, string email, string emailfeedback)
    {
        using (DataAccess dbAccess = new DataAccess(ConnectionString))
        {
            //return (dbAccess.InsertFeedback(email, title, comment).ToString());
            if (dbAccess.InsertFeedback(email, title, comment))
            {
                if (emailfeedback.Contains(','))
                {
                    string[] feedback = emailfeedback.Split(',');
                    foreach (string em in feedback)
                    {
                        SendMail(comment, email, em);
                    }
                }
                else
                {
                    SendMail(comment, email, emailfeedback);
                }
                return "true";
            }
            else
                return "false";
        }
    }

    [WebMethod]
    public List<Feedback> GetFeedback(string _title, DateTime _fromDate, DateTime _toDate)
    {
        Hashtable htInput = new Hashtable();
        Hashtable htOutput = new Hashtable();
        object obj = new object();
        htInput.Add("TitleFeedback", _title);
        htInput.Add("FromDate", _fromDate);
        htInput.Add("ToDate", _toDate);
        List<Feedback> lst = new List<Feedback>();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetFeedBack", htInput, ref htOutput, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow r in _dt.Rows)
            {
                Feedback fb = new Feedback();
                fb.Comment = r["Comment"].ToString();
                fb.EmailFeedback = r["EmailFeedback"].ToString();
                fb.EmailReplace = r["EmailReplace"].ToString();
                if (r["FeedbackDate"] != null)
                    fb.FeedbackDate = Convert.ToDateTime(r["FeedbackDate"]);
                if (r["IsReplace"] != null)
                    fb.IsReplace = Convert.ToBoolean(r["IsReplace"]);
                fb.TitleFeedback = r["TitleFeedback"].ToString();
                fb.Id = Convert.ToInt32(r["Id"].ToString());
                lst.Add(fb);
            }
        }
        return lst;
    }


    [WebMethod]
    public List<UserRoleFunction> GetFunctionByUserName(string _userName)
    {
        Hashtable htInput = new Hashtable();
        Hashtable htOutput = new Hashtable();
        object obj = new object();
        htInput.Add("userName", _userName);
        List<UserRoleFunction> lst = new List<UserRoleFunction>();
        DataTable _dt = dbmanager.GetData("sp_PIM_GetFunctionByUserName", htInput, ref htOutput, ref obj);
        if (_dt != null && _dt.Rows.Count > 0)
        {
            foreach (DataRow r in _dt.Rows)
            {
                UserRoleFunction urf = new UserRoleFunction();
                urf.FunctionId = r["FunctionId"].ToString();
                urf.FunctionName = r["FunctionName"].ToString();
                urf.ImageLink = r["ImageLink"].ToString();
                urf.isDelete = Convert.ToBoolean(r["IsDelete"]);
                urf.isInsert = Convert.ToBoolean(r["IsInsert"]);
                urf.isSelect = Convert.ToBoolean(r["IsSelect"]);
                urf.isUpdate = Convert.ToBoolean(r["IsUpdate"]);
                urf.isView = Convert.ToBoolean(r["IsView"]);
                urf.RoleId = r["RoleId"].ToString();
                urf.RoleName = r["RoleName"].ToString();
                urf.UserId = r["UserId"].ToString();
                urf.UserName = r["UserName"].ToString();
                lst.Add(urf);
            }
        }
        return lst;
    }

    private void SendMail(string body, string emailsend, string emailfeedback)
    {
        string _mailServer = "smtp.gmail.com";
        int _mailPort = 587;
        string _user = "planitmeal2013@gmail.com";
        string _pass = "11911431121176";
        string _mailFrom = "planitmeal2013@gmail.com";


        MailMessage mailMessage = new MailMessage();
        SmtpClient mailClient = new SmtpClient(_mailServer, _mailPort);
        mailClient.Timeout = 15000;
        mailClient.Credentials = new NetworkCredential(_user, _pass);
        mailClient.EnableSsl = true;

        mailMessage.IsBodyHtml = true;
        mailMessage.From = new MailAddress(_mailFrom);
        mailMessage.Subject = "[PlanItMeal]Feedback from " + emailsend.ToUpper().ToString();
        mailMessage.Body = body;

        mailMessage.To.Add(emailfeedback);
        mailClient.Send(mailMessage);
        //att.Dispose();
        mailMessage.Dispose();
    }

    [WebMethod]
    public void PlanItMealSendEmail(string body, string emailsend, string subject)
    {
        string _mailServer = "smtp.gmail.com";
        int _mailPort = 587;
        string _user = "planitmeal2013@gmail.com";
        string _pass = "11911431121176";
        string _mailFrom = "planitmeal2013@gmail.com";


        MailMessage mailMessage = new MailMessage();
        SmtpClient mailClient = new SmtpClient(_mailServer, _mailPort);
        mailClient.Timeout = 15000;
        mailClient.Credentials = new NetworkCredential(_user, _pass);
        mailClient.EnableSsl = true;

        mailMessage.IsBodyHtml = true;
        mailMessage.From = new MailAddress(_mailFrom);
        mailMessage.Subject = subject;
        mailMessage.Body = body;

        mailMessage.To.Add(emailsend);
        mailClient.Send(mailMessage);
        //att.Dispose();
        mailMessage.Dispose();
    }

    [WebMethod]
    public string SendMailContact(string message, string name, string email, string phone, string emailfeedback)
    {
        try
        {
            string _mailServer = "smtp.gmail.com";
            int _mailPort = 587;
            string _user = "planitmeal2013@gmail.com";
            string _pass = "11911431121176";
            string _mailFrom = "planitmeal2013@gmail.com";

            string body = "";
            body = "<h3>Name:</h3> <p><b>" + name.ToUpperInvariant() + "</b></p>";
            if (!string.IsNullOrEmpty(email))
                body += "<h3>Email:</h3> <p><b>" + email + "</b></p>";
            if (!string.IsNullOrEmpty(phone))
                body += "<h3>Phone:</h3> <p><b>" + phone + "</b></p>";
            //body += "<hr/><hr/>";
            body += message;

            if (emailfeedback.Contains(','))
            {
                string[] feedback = emailfeedback.Split(',');
                foreach (string em in feedback)
                {
                    MailMessage mailMessage = new MailMessage();
                    SmtpClient mailClient = new SmtpClient(_mailServer, _mailPort);
                    mailClient.Timeout = 15000;
                    mailClient.Credentials = new NetworkCredential(_user, _pass);
                    mailClient.EnableSsl = true;

                    mailMessage.IsBodyHtml = true;
                    mailMessage.From = new MailAddress(_mailFrom);
                    mailMessage.Subject = "[PlanItMeal]GET IN TOUCH from " + email.ToUpper().ToString();
                    mailMessage.Body = body;

                    mailMessage.To.Add(em);
                    mailClient.Send(mailMessage);
                    mailMessage.Dispose();
                }
            }
            else
            {
                MailMessage mailMessage = new MailMessage();
                SmtpClient mailClient = new SmtpClient(_mailServer, _mailPort);
                mailClient.Timeout = 15000;
                mailClient.Credentials = new NetworkCredential(_user, _pass);
                mailClient.EnableSsl = true;

                mailMessage.IsBodyHtml = true;
                mailMessage.From = new MailAddress(_mailFrom);
                mailMessage.Subject = "[PlanItMeal]GET IN TOUCH from " + email.ToUpper().ToString();
                mailMessage.Body = body;

                mailMessage.To.Add(emailfeedback);
                mailClient.Send(mailMessage);
                mailMessage.Dispose();
            }
            

            return "true";
        }
        catch
        {
            return "false";
        }
    }

    [WebMethod(EnableSession = true)]
    public string GetNextID()
    {
        Hashtable inputHtable = new Hashtable();
        Hashtable outPutHtable = new Hashtable();
        outPutHtable.Add("output", 0);
        object obj = new object();
        DataTable _dt = dbmanager.GetData("GetNextID", inputHtable, ref outPutHtable, ref obj);

        return outPutHtable["output"].ToString();
        //if (_dt != null && _dt.Rows.Count > 0)
        //{
        //    foreach (DataRow dr in _dt.Rows)
        //    {
        //        IngredientDeptDetail ingredientDetail = new IngredientDeptDetail();
        //        ingredientDetail.GroDept = dr["GroDept"].ToString();
        //        ingredientDetail.ingre = dr["ingre"].ToString();
        //        list.Add(ingredientDetail);
        //    }

        //}
        // return "";
    }
    
}
